package cn.edu.hit.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTools {
	static String server = "localhost";
	static String dbname = "visualdepartment";
	static String user = "root";
	//static String pass = "gongyu123";
	static String pass = "llflll";
	static String port = "3306";
	static String url = "jdbc:mysql://" + server + ":" + port + "/" + dbname
			+ "?user=" + user + "&password=" + pass
			+ "&useUnicode=true&characterEncoding=utf8";

	Connection conn = null;
	Statement stmt = null;
	private CallableStatement cStmt = null;
	ResultSet rs = null;
	String current_grade = null;

	public DBTools() {
	} 

	public DBTools(String current_grade) {
		this.current_grade = current_grade;
	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(url);
		} catch (Exception ee) {
			ee.printStackTrace();
		}
		if (conn == null) {
			return conn;
		}
		return conn;
	}

	/*
	 * �ô洢��̲�ѯ
	 */
	public ResultSet callQuery(String proc) {
		rs = null;
		try {
			conn = DBTools.getConnection();
			if (conn != null) {
				System.out.println("{call " + proc + "}");
				cStmt = conn.prepareCall("{call " + proc + "}",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = cStmt.executeQuery();
			}
		} catch (SQLException ex) {
			System.err.println("DAO.DBBean.executeQuery() ERR :"
					+ ex.getMessage());
		}
		return rs;
	}

	// ���ô洢��̸��£�����Ҫ����
	public boolean callUpdate(String proc) {
		rs = null;
		stmt = null;
		try {
			conn = DBTools.getConnection();
			if (conn != null) {
				System.out.println("{CALL " + proc + "}");
				cStmt = conn.prepareCall("{CALL " + proc + "}");
				return cStmt.execute();
			}
		} catch (SQLException ex) {
			
			System.err.println("DAO.DBBean.callUpdate() ERR :"
					+ ex.getMessage());
			return false;
		}
		return false;
	}

	public ResultSet executeQuery(String sql) {
		try {
			conn = getConnection();
			if (conn != null) {
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(sql);
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return rs;
	}

	/*
	 * ���и�����ݿ����
	 */
	public int executeUpdate(String sql) {
		int result = 0;
		try {
			conn = getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = stmt.executeUpdate(sql);
		} catch (SQLException ex) {
			result = 0;
		}
		try {
			stmt.close();
		} catch (SQLException ex1) {
		}
		return result;
	}

	/*
	 * ���йر���ݿ����
	 */
	public void close() {
		try { // ��׽�쳣
			if (rs != null) { // �ر�resultset
				rs.close(); 
			}
			if (stmt != null) { // �ر� stmt
				stmt.close(); 
			}
			if (conn != null) { // �ر�conn
				conn.close(); 
			}
		} catch (Exception e) {
			e.printStackTrace(System.err); 
		}
	}

	public void createTable(String grade)
	{
		try {
			conn = getConnection();
			if (conn != null) {
				String table_str = "activity_award_" + grade;
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

				String create_table_String = "CREATE TABLE '"
						+ table_str
						+ "'("
						+ "'ID' int(11) NOT NULL AUTO_INCREMENT,"
						+ "'student_number' varchar(10) CHARACTER SET gbk DEFAULT NULL,"
						+ "'student_name' varchar(20) CHARACTER SET gbk DEFAULT NULL,"
						+ "`time` varchar(8) CHARACTER SET gbk DEFAULT NULL,"
						+ "'award_type' varchar(2) CHARACTER SET gbk DEFAULT NULL,"
						+ "'activity' varchar(255) CHARACTER SET gbk DEFAULT NULL,"
						+ " 'award' varchar(255) CHARACTER SET gbk DEFAULT NULL,"
						+ "'type' varchar(2) CHARACTER SET gbk DEFAULT NULL,"
						+ " UNIQUE KEY 'ID' ('ID')"
						+ ") ENGINE=InnoDB DEFAULT CHARSET=latin1";

				stmt.execute(create_table_String);
			}
		} catch (Exception e) {

		}
	}

}
